import { RowDataPacket, ResultSetHeader } from 'mysql2';
import { pool } from '../utils/database';
import { CartItem, CartRequest, UpdateCartRequest } from '../types/order';

export class CartModel {
  // 添加商品到购物车
  static async addItem(userId: number, cartData: CartRequest): Promise<number> {
    const { productId, skuId, quantity } = cartData;
    
    // 获取商品价格
    let price: number;
    if (skuId) {
      const [skuRows] = await pool.execute<RowDataPacket[]>(
        'SELECT price FROM product_skus WHERE id = ? AND product_id = ?',
        [skuId, productId]
      );
      if (skuRows.length === 0) {
        throw new Error('SKU不存在');
      }
      price = parseFloat(skuRows[0].price);
    } else {
      const [productRows] = await pool.execute<RowDataPacket[]>(
        'SELECT price FROM products WHERE id = ?',
        [productId]
      );
      if (productRows.length === 0) {
        throw new Error('商品不存在');
      }
      price = parseFloat(productRows[0].price);
    }
    
    // 检查是否已存在相同商品
    const [existingRows] = await pool.execute<RowDataPacket[]>(
      'SELECT id, quantity FROM carts WHERE user_id = ? AND product_id = ? AND (sku_id = ? OR (sku_id IS NULL AND ? IS NULL))',
      [userId, productId, skuId || null, skuId || null]
    );
    
    if (existingRows.length > 0) {
      // 更新数量
      const newQuantity = existingRows[0].quantity + quantity;
      await pool.execute(
        'UPDATE carts SET quantity = ?, price = ?, updated_at = NOW() WHERE id = ?',
        [newQuantity, price, existingRows[0].id]
      );
      return existingRows[0].id;
    } else {
      // 新增购物车项
      const [result] = await pool.execute<ResultSetHeader>(
        'INSERT INTO carts (user_id, product_id, sku_id, quantity, price) VALUES (?, ?, ?, ?, ?)',
        [userId, productId, skuId || null, quantity, price]
      );
      return result.insertId;
    }
  }
  
  // 获取用户购物车列表
  static async findByUserId(userId: number): Promise<CartItem[]> {
    const [rows] = await pool.execute<RowDataPacket[]>(`
      SELECT 
        c.*,
        p.name as product_name,
        p.main_image as product_image,
        p.status as product_status,
        s.sku_code,
        s.specifications as sku_specifications,
        s.price as sku_price,
        s.stock_quantity
      FROM carts c
      LEFT JOIN products p ON c.product_id = p.id
      LEFT JOIN product_skus s ON c.sku_id = s.id
      WHERE c.user_id = ?
      ORDER BY c.created_at DESC
    `, [userId]);
    
    return rows.map(row => ({
      id: row.id,
      userId: row.user_id,
      productId: row.product_id,
      skuId: row.sku_id,
      quantity: row.quantity,
      price: parseFloat(row.price),
      selected: Boolean(row.selected),
      createdAt: row.created_at,
      updatedAt: row.updated_at,
      product: {
        id: row.product_id,
        name: row.product_name,
        image: row.product_image,
        status: row.product_status
      },
      sku: row.sku_id ? {
        id: row.sku_id,
        skuCode: row.sku_code,
        specifications: row.sku_specifications,
        price: parseFloat(row.sku_price),
        stockQuantity: row.stock_quantity
      } : undefined
    }));
  }
  
  // 更新购物车项
  static async updateItem(id: number, userId: number, updateData: UpdateCartRequest): Promise<boolean> {
    const fields: string[] = [];
    const values: any[] = [];
    
    if (updateData.quantity !== undefined) {
      fields.push('quantity = ?');
      values.push(updateData.quantity);
    }
    
    if (updateData.selected !== undefined) {
      fields.push('selected = ?');
      values.push(updateData.selected ? 1 : 0);
    }
    
    if (fields.length === 0) {
      return false;
    }
    
    fields.push('updated_at = NOW()');
    values.push(id, userId);
    
    const [result] = await pool.execute<ResultSetHeader>(
      `UPDATE carts SET ${fields.join(', ')} WHERE id = ? AND user_id = ?`,
      values
    );
    
    return result.affectedRows > 0;
  }
  
  // 删除购物车项
  static async deleteItem(id: number, userId: number): Promise<boolean> {
    const [result] = await pool.execute<ResultSetHeader>(
      'DELETE FROM carts WHERE id = ? AND user_id = ?',
      [id, userId]
    );
    
    return result.affectedRows > 0;
  }
  
  // 批量删除购物车项
  static async deleteItems(ids: number[], userId: number): Promise<boolean> {
    if (ids.length === 0) return false;
    
    const placeholders = ids.map(() => '?').join(',');
    const [result] = await pool.execute<ResultSetHeader>(
      `DELETE FROM carts WHERE id IN (${placeholders}) AND user_id = ?`,
      [...ids, userId]
    );
    
    return result.affectedRows > 0;
  }
  
  // 清空用户购物车
  static async clearCart(userId: number): Promise<boolean> {
    const [result] = await pool.execute<ResultSetHeader>(
      'DELETE FROM carts WHERE user_id = ?',
      [userId]
    );
    
    return result.affectedRows > 0;
  }
  
  // 获取选中的购物车项
  static async getSelectedItems(userId: number): Promise<CartItem[]> {
    const [rows] = await pool.execute<RowDataPacket[]>(`
      SELECT 
        c.*,
        p.name as product_name,
        p.main_image as product_image,
        p.status as product_status,
        s.sku_code,
        s.specifications as sku_specifications,
        s.price as sku_price,
        s.stock_quantity
      FROM carts c
      LEFT JOIN products p ON c.product_id = p.id
      LEFT JOIN product_skus s ON c.sku_id = s.id
      WHERE c.user_id = ? AND c.selected = 1
      ORDER BY c.created_at DESC
    `, [userId]);
    
    return rows.map(row => ({
      id: row.id,
      userId: row.user_id,
      productId: row.product_id,
      skuId: row.sku_id,
      quantity: row.quantity,
      price: parseFloat(row.price),
      selected: Boolean(row.selected),
      createdAt: row.created_at,
      updatedAt: row.updated_at,
      product: {
        id: row.product_id,
        name: row.product_name,
        image: row.product_image,
        status: row.product_status
      },
      sku: row.sku_id ? {
        id: row.sku_id,
        skuCode: row.sku_code,
        specifications: row.sku_specifications,
        price: parseFloat(row.sku_price),
        stockQuantity: row.stock_quantity
      } : undefined
    }));
  }
  
  // 全选/取消全选
  static async selectAll(userId: number, selected: boolean): Promise<boolean> {
    const [result] = await pool.execute<ResultSetHeader>(
      'UPDATE carts SET selected = ?, updated_at = NOW() WHERE user_id = ?',
      [selected ? 1 : 0, userId]
    );
    
    return result.affectedRows > 0;
  }
  
  // 获取购物车统计
  static async getCartStats(userId: number): Promise<{ totalItems: number; selectedItems: number; totalAmount: number }> {
    const [rows] = await pool.execute<RowDataPacket[]>(`
      SELECT 
        COUNT(*) as total_items,
        SUM(CASE WHEN selected = 1 THEN 1 ELSE 0 END) as selected_items,
        SUM(CASE WHEN selected = 1 THEN price * quantity ELSE 0 END) as total_amount
      FROM carts 
      WHERE user_id = ?
    `, [userId]);
    
    const stats = rows[0];
    return {
      totalItems: stats.total_items,
      selectedItems: stats.selected_items,
      totalAmount: parseFloat(stats.total_amount) || 0
    };
  }
}